﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;

namespace 养老金支付系统
{
    public partial class FormGZBDR : FormBase
    {
        public FormGZBDR()
        {
            InitializeComponent();
        }

        OpenFileDialog ofd1;
        string filepath1 = string.Empty;
        int yhbh1 = 0;
        DataTable dt1;
        int bs1;
        decimal je1;

        OpenFileDialog ofd2;
        string filepath2 = string.Empty;
        DataTable dt2;
        int bs2;
        decimal je2;

        string initpath = PPS.InputPath;//报盘文件目录

        void buttonEnabled(bool b)
        {
            button1.Enabled = b;
            button2.Enabled = b;
            button3.Enabled = b;
            button4.Enabled = b;
        }

        int GetYHBH(string yh)
        {
            switch (yh)
            {
                case "80000001":
                    return 0;
                case "80000005":
                    return 1;
                case "80000006":
                    return 2;
                case "80000007":
                    return 3;
                case "80000008":
                    return 4;
                case "80000009":
                    return 5;
                case "80000004":
                    return 9;
                default:
                    return 10;
            }
        }

        void init()
        {
            this.Text = "工资表导入";
            textBox1.Text = SqlHelper.getScalar("select dbo.GetNowQH()") as string;
            textBox5.Text = textBox1.Text;
            textBox10.Text = textBox1.Text + "工资";
            textBox11.Text = "一次性支付";
            comboBox1.DisplayMember = "显示";
            comboBox1.DataSource = SqlHelper.getDataTable("select cast(银行编号 as char(2))+' '+银行名称 as 显示 from 支付银行");
            ofd1 = new OpenFileDialog()
            {
                Title = "请选择养老金报盘文件",
                //InitialDirectory = initpath,
                FileName = textBox1.Text + "武穴*报盘信息.xls",
                Filter = "Excel(97-2003)|*.xls"
            };
            ofd2 = new OpenFileDialog()
            {
                Title = "请选择一次性支付导出文件",
                //InitialDirectory = initpath,
                FileName = textBox1.Text + "*一次性支付*.xls",
                Filter = "Excel(97-2003)|*.xls"
            };
        }

        void find1()
        {
            if (filepath1 != string.Empty)
            {
                FileStream excelFileStream = File.OpenRead(filepath1);
                HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
                DataTable table = new DataTable();
                table.TableName = "养老金导入_tmp";
                //添加列名                                 
                string[] columnName = new string[] { "支付期号", "个人编号", "姓名", "身份证号", "银行编号", "银行帐号", "支付金额" };
                for (int i = 0; i < columnName.Length; i++)
                {
                    DataColumn column = new DataColumn();
                    column.ColumnName = columnName[i];
                    column.DataType = System.Type.GetType("System.String");
                    table.Columns.Add(column);
                }
                table.Columns[4].DataType = System.Type.GetType("System.Int32");
                table.Columns[6].DataType = System.Type.GetType("System.Decimal");
                //从第一行到最后一行
                for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                {
                    //添加一行数据
                    HSSFRow row = (HSSFRow)sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    dataRow[0] = row.GetCell(0).ToString().Trim();//支付期号
                    dataRow[1] = row.GetCell(3).ToString().Trim();//个人编号
                    dataRow[2] = row.GetCell(7).ToString().Trim();//姓名
                    dataRow[3] = row.GetCell(5).ToString().Trim();//身份证号
                    dataRow[4] = this.yhbh1;//银行编号
                    dataRow[5] = row.GetCell(6).ToString().Trim();//银行帐号
                    dataRow[6] = decimal.Parse(row.GetCell(8).ToString().Trim());//支付金额
                    table.Rows.Add(dataRow);
                }
                excelFileStream.Close();
                workbook = null;
                sheet = null;
                this.bs1 = table.Rows.Count;
                this.je1 = (decimal)table.Compute("sum(支付金额)", "");
                this.dt1 = table;
                textBox3.Text = bs1.ToString();
                textBox4.Text = je1.ToString();
            }
        }

        void into1()
        {
            if (dt1 != null)
            {
                //清空临时表
                SqlHelper.exeProcReturn("P_清空临时表");
                //将数据导入到临时表
                SqlHelper.DataTableCopyToDataBase(this.dt1);
                //导入到支付明细
                int ret = SqlHelper.exeProcReturn("P_养老金导入");
                //验证返回结果
                string msg = "共有" + this.bs1.ToString() + "笔，其中" + ret.ToString() + "笔导入成功！";
                if (ret == this.bs1)
                {
                    MessageBox.Show(this, msg, "养老金导入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show(this, msg, "养老金导入", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        void find2()
        {
            if (filepath2 != string.Empty)
            {
                FileStream excelFileStream = File.OpenRead(filepath2);
                HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
                DataTable table = new DataTable();
                table.TableName = "一次性支付导入_tmp";
                //添加列名   设置列属性                              
                string[] columnName = new string[] { "支付类型码", "个人编号", "姓名", 
                "账户返还", "丧葬费", "抚恤金", "退补发", "支付金额",
                "银行编号", "银行户名", "银行帐号", "备注" };
                for (int i = 0; i < columnName.Length; i++)
                {
                    DataColumn column = new DataColumn();
                    column.ColumnName = columnName[i];
                    column.DataType = System.Type.GetType("System.String");
                    table.Columns.Add(column);
                }
                table.Columns[0].DataType = System.Type.GetType("System.Int32");
                table.Columns[3].DataType = System.Type.GetType("System.Decimal");
                table.Columns[4].DataType = System.Type.GetType("System.Decimal");
                table.Columns[5].DataType = System.Type.GetType("System.Decimal");
                table.Columns[6].DataType = System.Type.GetType("System.Decimal");
                table.Columns[7].DataType = System.Type.GetType("System.Decimal");
                table.Columns[8].DataType = System.Type.GetType("System.Int32");
                //从第一行到最后一行
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    //添加一行数据
                    HSSFRow row = (HSSFRow)sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    dataRow[0] = int.Parse(row.GetCell(0).ToString().Trim().Replace(" ",""));
                    dataRow[1] = row.GetCell(1).ToString().Trim().Replace(" ", "");
                    dataRow[2] = row.GetCell(2).ToString().Trim().Replace(" ", "");
                    dataRow[3] = decimal.Parse(row.GetCell(3).ToString().Trim().Replace(" ", ""));//账户返还
                    dataRow[4] = decimal.Parse(row.GetCell(4).ToString().Trim().Replace(" ", ""));//丧葬费
                    dataRow[5] = decimal.Parse(row.GetCell(5).ToString().Trim().Replace(" ", ""));//抚恤金
                    dataRow[6] = decimal.Parse(row.GetCell(6).ToString().Trim().Replace(" ", ""));//退补发
                    dataRow[7] = decimal.Parse(row.GetCell(7).ToString().Trim().Replace(" ", ""));//支付金额
                    dataRow[8] = int.Parse(row.GetCell(8).ToString().Trim().Replace(" ", ""));//银行编号
                    dataRow[9] = row.GetCell(9).ToString().Trim().Replace(" ", "");
                    dataRow[10] = row.GetCell(10).ToString().Trim().Replace(" ", "");
                    dataRow[11] = row.GetCell(11).ToString().Trim().Replace(" ", "");
                    table.Rows.Add(dataRow);
                }
                excelFileStream.Close();
                workbook = null;
                sheet = null;
                this.bs2 = table.Rows.Count;
                this.je2 = (decimal)table.Compute("sum(支付金额)", "");
                this.dt2 = table;
                textBox7.Text = bs2.ToString();
                textBox8.Text = je2.ToString();
            }
        }

        void into2()
        {
            if (dt2 != null)
            {
                //清空临时表
                SqlHelper.exeProcReturn("P_清空临时表");
                //将数据导入到临时表
                SqlHelper.DataTableCopyToDataBase(this.dt2);
                //导入到支付明细
                int ret = SqlHelper.exeProcReturn("P_一次性支付导入");
                //验证返回结果
                string msg = "共有" + this.bs2.ToString() + "笔，其中" + ret.ToString() + "笔导入成功！";
                if (ret == this.bs2)
                {
                    MessageBox.Show(this, msg, "一次性支付导入", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show(this, msg, "一次性支付导入", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            //ofd1.InitialDirectory = initpath;//初始目录
            if (ofd1.ShowDialog() == DialogResult.OK)
            {
                this.textBox2.Text = ofd1.FileName;
                find1();
            }
            buttonEnabled(true);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            //ofd2.InitialDirectory = initpath;//初始目录
            if (ofd2.ShowDialog() == DialogResult.OK)
            {
                this.textBox6.Text = ofd2.FileName;
                find2();
            }
            buttonEnabled(true);
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.yhbh1 = comboBox1.SelectedIndex;
            string yhmc = string.Empty;
            switch (this.yhbh1)
            {
                case 0:
                    yhmc = "农行";
                    break;
                case 1:
                    yhmc = "工商";
                    break;
                case 2:
                    yhmc = "建设";
                    break;
                case 3:
                    yhmc = "邮政";
                    break;
                case 4:
                    yhmc = "中国";
                    break;
                case 5:
                    yhmc = "农商";
                    break;
                default:
                    yhmc = "";
                    break;
            }
            if (ofd1 != null)
                ofd1.FileName = textBox1.Text + yhmc + "*报盘信息.xls";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            into1();
            buttonEnabled(true);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            buttonEnabled(false);
            into2();
            buttonEnabled(true);
        }

        private void FormGZBDR_Load(object sender, EventArgs e)
        {
            init();
        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {
            this.filepath2 = textBox6.Text;
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {
            this.filepath1 = textBox2.Text;
        }


    }
}
